﻿using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using MySql.Data.MySqlClient;



namespace DALC.AcessoDALC
{
    public class ConexaoDALC
    {
        //GlobalDALC glb = new GlobalDALC();
        //DbProviderFactory dbProvider;

        private MySqlConnection DbConn; //MySQL
        private MySqlDataAdapter bdAdapter;
        private DataSet ds; //MySQL
        private DbCommand DbCmd;

        public void insert(String str, String tabela)
        {
            String query;
            query = "insert into " + tabela;
            query = query + " values(default";
            foreach (String coluna in str.Split('§'))
            {
                query = query + ",'" + coluna + "'";
            }
            query = query + ")";
            //Caiçara Boiolavcvxz

            ExecuteNonQuery(query, CommandType.Text);
        }


#region Conexão
        
        public DbConnection AbrirConexao()
        {
            try
            {
                DbConn = new MySqlConnection();//"Server=75.126.154.163;Database=Miranda;Uid=mt;Pwd=transportes;Allow Zero Datetime=true;");


                //if (DbConn.State != ConnectionState.Open)
                //{
                //    DbConn.Open();
                //}
                return DbConn;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public MySqlConnection AbrirConexao1()
        {
            try
            {
                DbConn = new MySqlConnection("Server=75.126.154.163;Database=Miranda;Uid=mt;Pwd=transportes;");


                if (DbConn.State != ConnectionState.Open)
                {
                    DbConn.Open();
                }
                return DbConn;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

#endregion

#region Geral

        ///<resumo>
        ///     Metodo responsavel por criar parametro
        ///     <exemplo>
        ///         List<DbParameter> param = new List<DbParameter>();
        ///         param.Add(CriarParamentro(nome, tipo, valor));
        ///     </exmemplo>
        /// </resumo>
        /// <param name="nameParameter">Nome do Parametro</param>
        /// <param name="typeParameter">Tipo do Parametro</param>
        /// <param name="valueParameter">Valor do Parametro</param>
        /// <returns>Parametro Preenchido</returns>
        /// 
        public static DbParameter CriaParametro(String sNomeParametro, DbType dbTipoParametro, Object objValorParametro)
        {
            DbProviderFactory dbProvider;

            //Cria um novo factories conforme nome do provedor
            //Passa ao componente o provider utilizado para conexão, 
            //nesse caso uma conexão com o SqlServer
            string provider = ConfigurationSettings.AppSettings["Provider"].ToString();
            dbProvider = DbProviderFactories.GetFactory(provider);

            //Cria o parametro e adiciona os valores
            DbParameter param = dbProvider.CreateParameter();
            param.ParameterName = sNomeParametro;
            param.DbType = dbTipoParametro;
            param.Value = objValorParametro;

            //retorna o parametro criado
            return param;
        }



#endregion

        #region ExecuteNonQuery

        //Controle de Transação
        public Int32 ExecuteNonQuery(string strSql, CommandType cType)
        {
            Int32 intRet = 0;
            try
            {
                //Controle de Transação
                //using (TransactionScope ts = new TransactionScope())
                //{
                    using (DbCmd = AbrirConexao().CreateCommand())
                    {
                        DbCmd.CommandText = strSql;
                        DbCmd.CommandType = cType;
                        intRet = DbCmd.ExecuteNonQuery();
                    }
                    if (false)
                        throw new Exception("Ocorreu um erro na gravação dos dados.");
                    //ts.Complete();
                    //ts.Dispose();
                //}
                return intRet;
            }

            //catch (TransactionAbortedException ex)
            //{ throw ex; }

            catch (DbException ex)
            { throw ex; }

            catch (Exception ex)
            { throw ex; }

            finally
            {
                if (DbConn.State == ConnectionState.Open)
                {
                    DbConn.Close();

                }
            }

        }

        //public int ExecuteNonQuery(string strSql, CommandType cType, DbParameter[] dbParam)
        //{
        //    int intRet = 0;
        //    try
        //    {
        //        //Controle de Transação
        //        using (TransactionScope ts = new TransactionScope())
        //        {
        //            using (glb.DbCmd = AbrirConexao().CreateCommand())
        //            {
        //                glb.DbCmd.CommandText = strSql;
        //                glb.DbCmd.CommandType = cType;
        //                glb.DbCmd.Parameters.AddRange(dbParam);

        //                intRet = glb.DbCmd.ExecuteNonQuery();
        //            }
        //            if (false)
        //                throw new Exception("Ocorreu um erro na gravação dos dados.");
        //            ts.Complete();
        //            ts.Dispose();
        //        }
        //        return intRet;
        //    }

        //    catch (TransactionAbortedException ex)
        //    { throw ex; }

        //    catch (DbException ex)
        //    { throw ex; }

        //    catch (Exception ex)
        //    { throw ex; }

        //    finally
        //    {
        //        if (glb.DbConn.State == ConnectionState.Open)
        //        {
        //            glb.DbConn.Close();

        //        }
        //    }

        //}

        //public int ExecuteNonQuery(string strSql, CommandType cType, List<DbParameter> dbParam)
        //{
        //    int intRet = 0;

        //    try
        //    {
        //        using (TransactionScope ts = new TransactionScope())
        //        {
        //            DbProviderFactory factory = DbProviderFactories.GetFactory(ConfigurationSettings.AppSettings["Provider"].ToString());
        //            DbConnection Connection = factory.CreateConnection();
        //            Connection.ConnectionString = ConfigurationSettings.AppSettings["ConnString"].ToString();

        //            using (Connection)
        //            {
        //                DbCommand command = factory.CreateCommand();
        //                command.CommandText = strSql;
        //                command.CommandType = cType;
        //                command.Connection = Connection;
        //                command.Connection.Open();

        //                if (dbParam != null)
        //                {
        //                    foreach (DbParameter param in dbParam)
        //                    {
        //                        //adiciona os parametros
        //                        command.Parameters.Add(param);
        //                    }
        //                }
        //                intRet = command.ExecuteNonQuery();

        //            }
        //            ts.Complete();
        //            ts.Dispose();
        //        }
        //        return intRet;
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}
        #endregion

        #region RetornaDataReader

        //public DbDataReader RetornaDataReader(string strSql, CommandType cType)
        //{
        //    try
        //    {
        //        using (DbCmd = AbrirConexao().CreateCommand())
        //        {
        //            DbCmd.CommandText = strSql;
        //            DbCmd.CommandType = cType;

        //            DbReader = DbCmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //        return glb.DbReader;
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        //public DbDataReader RetornaDataReader(string strSql, CommandType cType, DbParameter[] dbParam)
        //{
        //    try
        //    {
        //        using (glb.DbCmd = AbrirConexao().CreateCommand())
        //        {
        //            glb.DbCmd.CommandText = strSql;
        //            glb.DbCmd.CommandType = cType;

        //            glb.DbCmd.Parameters.AddRange(dbParam);

        //            glb.DbReader = glb.DbCmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //        return glb.DbReader;
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }
        //}

        //public DbDataReader RetornaDataReader(string strSql, CommandType cType, List<DbParameter> dbParam)
        //{
        //    try
        //    {
        //        DbDataReader dr;
        //        using (glb.DbCmd = AbrirConexao().CreateCommand())
        //        {
        //            glb.DbCmd.CommandText = strSql;
        //            glb.DbCmd.CommandType = cType;

        //            if (dbParam != null)
        //            {
        //                foreach (DbParameter param in dbParam)
        //                {
        //                    //adiciona os parametros
        //                    glb.DbCmd.Parameters.Add(param);
        //                }
        //            }
        //            dr = glb.DbCmd.ExecuteReader();
        //        }
        //        return dr;
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }
        //}
        #endregion

        #region RetornaDataTable

        public DataTable RetornaDataTable(string strSql, CommandType cType)
        {
            ds = new DataSet();


            try
            {


                //definindo o comando para a conexão a instrução SQL
                MySqlCommand cmd = new MySqlCommand(strSql, AbrirConexao1());
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                //preenchendo o objeto DataTable a partir do DataAdapter
                da.Fill(ds);

               return ds.Tables[0];

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (DbConn.State == ConnectionState.Open)
                {
                    DbConn.Close();
                }
                ds.Dispose();
            }
        }

        //public DataTable RetornaDataTable(string strSql, CommandType cType, DbParameter[] dbParam)
        //{
        //    glb.ds = new DataSet();

        //    try
        //    {
        //        using (glb.DbCmd = AbrirConexao().CreateCommand())
        //        {
        //            glb.DbCmd.CommandType = cType;
        //            glb.DbCmd.CommandText = strSql;
        //            glb.DbCmd.Parameters.AddRange(dbParam);

        //            using (glb.DbAdapter = dbProvider.CreateDataAdapter())
        //            {
        //                glb.DbAdapter.SelectCommand = glb.DbCmd;
        //                glb.DbAdapter.Fill(glb.ds);
        //            }
        //        }

        //        return glb.ds.Tables[0];

        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (glb.DbConn.State == ConnectionState.Open)
        //        {
        //            glb.DbConn.Close();
        //        }
        //        glb.ds.Dispose();
        //    }
        //}

        //public DataTable RetornaDataTable(string strSql, CommandType cType, List<DbParameter> dbParam)
        //{
        //    glb.ds = new DataSet();

        //    try
        //    {
        //        using (glb.DbCmd = AbrirConexao().CreateCommand())
        //        {
        //            glb.DbCmd.CommandType = cType;
        //            glb.DbCmd.CommandText = strSql;
        //            if (dbParam != null)
        //            {
        //                foreach (DbParameter param in dbParam)
        //                {
        //                    //adiciona os parametros
        //                    glb.DbCmd.Parameters.Add(param);
        //                }
        //            }
        //            using (glb.DbAdapter = dbProvider.CreateDataAdapter())
        //            {
        //                glb.DbAdapter.SelectCommand = glb.DbCmd;
        //                glb.DbAdapter.Fill(glb.ds);
        //            }
        //        }

        //        return glb.ds.Tables[0];

        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (glb.DbConn.State == ConnectionState.Open)
        //        {
        //            glb.DbConn.Close();
        //        }
        //        glb.ds.Dispose();
        //    }
        //} 

        #endregion

        
    }
}
